﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;
using Entity;

namespace DAL
{
    public class SqlSentence
    {
        public static string test = "select TestContent from Test";

        public static string getLatestUserIdByYearTail = "select UserId from [User] where UserId like @yearTail order by UserId desc";

        public static string insertUser = "insert into [User] values(@UserId, @UserName, @Password, @Type, @Email, @Telephone, @Cellphone, getdate())";

        public static string getUserTableByUserName = "select * from [User] where UserName=@UserName";
        
        public static string getIdByUserId = "select [Id] from [User] where [UserId]=@UserId";
        
        public static string insertService = "insert into [AppointmentService] values(@ServiceName, @TimeBuckets, @DirectorId, @Introduction, @PerMultiTime, @MultiNum, 'true')";

        public static string getUserById = "select * from [User] where Id=@Id";

        public static string getAllActiveService = "select * from AppointmentService where IsActive='True'";

        public static string getServicesForDirector = "select * from AppointmentService left join [User] on DirectorId=[User].Id where [User].UserId=@UserId";

        public static string getServiceByServiceId = "select * from AppointmentService where ServiceId=@ServiceId";

        public static string getReservedNum = "select count(*) from AppointmentRecord where ServiceId=@ServiceId and Day=@Day and TimeBucket=@TimeBucket and Status in ('" + AppointmentRecord.RecordStatus.Apply + "', '" + AppointmentRecord.RecordStatus.Approve + "', '" + AppointmentRecord.RecordStatus.Complete + "')";

        public static string insertRecord = "insert into AppointmentRecord(ServiceId, ApplicantId, Day, TimeBucket, Status, CreatTime) values(@ServiceId, @ApplicantId, @Day, @TimeBucket, @Status, getdate())";

        public static string getRecords = "select R.*, S.ServiceName, S.Introduction, UD.UserName as Director, US.UserName as Student " +
                                                    "from AppointmentRecord AS R " +
                                                    "left join AppointmentService AS S on R.ServiceId=S.ServiceId " +
                                                    "left join [User] AS UD on S.DirectorId=UD.Id " +
                                                    "left join [User] AS US on R.ApplicantId=US.Id ";
        
        public static string getRecordsForStudent = getRecords + "where R.ApplicantId=@user order by CreatTime desc";

        public static string getRecordsForDirector = getRecords + "where S.DirectorId=@user order by cast(Day as datetime) desc";

        public static string getRecordCommonTable = "select * from AppointmentRecord where AppointmentId=@AppointmentId";

        public static string updateRecordStatusByDirector = "update AppointmentRecord set Status=@Status, ApprovalStatus=@Status, ApprovalComment=@ApprovalComment, ApproveTime=getdate() where AppointmentId=@AppointmentId";

        public static string studentMakeComment = "update AppointmentRecord set Status='" + AppointmentRecord.RecordStatus.Complete + "', Comment=@Comment where AppointmentId=@AppointmentId";

        public static string insertMessage = "insert into Message(UserId, MessageContent, CreatTime) values(@UserId, @MessageContent, getdate())";

        public static string getUnreadMessages = "select * from Message where UserId=@UserId and IsRead='False'";

        public static string getReadMessages = "select * from Message where UserId=@UserId and IsRead='True'";

        public static string updateMessageReadStatus = "update Message set IsRead=@IsRead where MessageId=@MessageId";

        public static string updateService = "update AppointmentService set ServiceName=@ServiceName, Introduction=@Introduction, MultiNum=@MultiNum, IsActive=@IsActive where ServiceId=@ServiceId";
                                                                            
    }                                                                       
}                                                                           
